package marchsoft.modules.four.admin.service.Impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.RequiredArgsConstructor;
import marchsoft.base.BasicServiceImpl;
import marchsoft.base.PageVO;
import marchsoft.modules.four.admin.entity.dto.StudentDTO;
import marchsoft.modules.four.admin.entity.dto.StudentDTOH5;
import marchsoft.modules.four.admin.mapper.StudentMapper;
import marchsoft.modules.four.admin.service.StudentService;
import marchsoft.modules.four.admin.service.mapstruct.StudentMapstruct;
import marchsoft.modules.four.common.entity.Student;
import marchsoft.modules.four.common.mapper.MapperStudent;
import marchsoft.utils.FileUtils;
import org.springframework.stereotype.Service;
import marchsoft.enums.ResultEnum;
import marchsoft.exception.BadRequestException;
import marchsoft.modules.four.common.utils.BaseUtils;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.web.multipart.MultipartFile;
import javax.servlet.http.HttpServletResponse;
import java.util.ArrayList;
import java.util.List;
import java.util.stream.Collectors;

@Service
@RequiredArgsConstructor
public class StudentServiceImpl extends BasicServiceImpl<MapperStudent, Student> implements StudentService {

    private final StudentMapstruct studentMapstruct;
    private final StudentMapper studentMapper;
    private final PasswordEncoder passwordEncoder;
    private List<Student> eStudentList;
    private final StudentMapper mapper;
    private int errorNum;
    @Value("${file.path}")
    private String filePath;


    @Override //分页条件查询学生信息
    public IPage<Student> queryAll1(PageVO pageVO, StudentDTO studentdto) {
        IPage<Student> page = new Page<>(pageVO.getCurrent(), pageVO.getSize());
        QueryWrapper<Student> queryWrapper = new QueryWrapper<>();
        String studentName = studentdto.getName();
        if (studentName != null){
            queryWrapper.eq("name",studentName);
        }
        String admissionNum = studentdto.getAdmissionNum();
        if (admissionNum != null){
            queryWrapper.eq("admission_num",admissionNum);
        }
        String idCard = studentdto.getIdCard();
        if (idCard != null){
            queryWrapper.eq("id_card",idCard);
        }
        String studentNum = studentdto.getStudentNum();
        if (studentNum != null){
            queryWrapper.eq("student_num",studentNum);
        }
        Long universityId = studentdto.getUniversityId();
        if (universityId != null){
            queryWrapper.eq("university_id",universityId);
        }
        String college = studentdto.getCollege();
        if (college != null){
            queryWrapper.eq("college",college);
        }
        String enrollmentYear = studentdto.getEnrollmentYear();
        if (enrollmentYear != null){
            queryWrapper.eq("enrollment_year",enrollmentYear);
        }
        queryWrapper.orderByDesc("create_time");
        return page(page, queryWrapper);
    }

    @Override // 查询所有安全分类名称
    public List<Student> selectAllNameSafeType() {
        return mapper.selectDeptName();
    }

    @Override // 新增学生信息
    public void insertStudent(Student student) {

        save(student);
    }

    @Override // 更新学生信息
    public void updateStudent(Student student) {
        updateById(student);
    }

    @Override // 根据id 删除学生信息
    public void deleteStudent(List<Integer> id) {
        removeByIds(id);
    }

    /**
     * 批量导入学生信息
     *
     * */

    @Override
    public void importStudents(MultipartFile file, HttpServletResponse res) {
        //清空list
        eStudentList = new ArrayList<>();
        if (!file.isEmpty()) {
            try {
                //excel 文件
                XSSFWorkbook workbook = new XSSFWorkbook(file.getInputStream());
                //excel 工作表
                XSSFSheet sheet = workbook.getSheetAt(0);
                //空值判断
                judgeNullValue(sheet);
                if (errorNum > 0) {
                    String fileName = "errorStudentLog.xlsx";
                    File fileDir = new File(filePath);
                    if (!fileDir.exists()) {
                        fileDir.mkdirs();
                    }
                    // 保存excel文件到服务器
                    FileOutputStream out = new FileOutputStream(filePath + "DIS" + fileName);
                    workbook.write(out);
                    out.close();
                    workbook.close();
                    log.error("【学生信息模板填写有误，请下载错误文件报告!】");
                    BaseUtils.errorLog(ResultEnum.OPERATION_FAIL, "DIS" + fileName, "【学生信息模板填写有误，请下载错误文件报告!】");
                }
                if (errorNum == 0) {
                    studentMapper.insertAllBatch(eStudentList);
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
            return;
        }
        log.error("请上传文件！");
        throw new BadRequestException("请上传文件！");
    }


    /**
     * 条件查询学生信息
     * 导出信息
     * 传回list
     *
     * */

    @Override
    public List<StudentDTOH5> queryAll(StudentDTOH5 studentDTOH5) {
        List<Student> depts = this.mapper.selectList(analysisQueryCriteria(studentDTOH5));
        return studentMapstruct.toDto(depts);
    }

    /**
     * 条件查询
     *
     * */

    @Override
    public LambdaQueryWrapper<Student> analysisQueryCriteria(StudentDTOH5 studentDTOH5) {
        LambdaQueryWrapper<Student> wrapper = new LambdaQueryWrapper<>();
        String studentName = studentDTOH5.getName();
        if (studentName != null){
            wrapper.eq(Student::getName,studentName);
        }
        String admissionNum = studentDTOH5.getAdmissionNum();
        if (admissionNum != null){
            wrapper.eq(Student::getAdmissionNum,admissionNum);
        }
        String idCard = studentDTOH5.getIdCard();
        if (idCard != null){
            wrapper.eq(Student::getIdCard,idCard);
        }
        String studentNum = studentDTOH5.getStudentNum();
        if (studentNum != null){
            wrapper.eq(Student::getStudentNum,studentNum);
        }
        Long universityId = studentDTOH5.getUniversityId();
        if (universityId != null){
            wrapper.eq(Student::getUniversityId,universityId);
        }
        String college = studentDTOH5.getCollege();
        if (college != null){
            wrapper.eq(Student::getCollege,college);
        }
        String enrollmentYear = studentDTOH5.getEnrollmentYear();
        if (enrollmentYear != null){
            wrapper.eq(Student::getEnrollmentYear,enrollmentYear);
        }
        return wrapper;
    }

    /**
     * 导出学生信息
     * 赋值
     *
     * */

    @Override
    public void download(List<StudentDTOH5> queeryAll,HttpServletResponse response) throws IOException {
        List<Map<String, Object>> list = new ArrayList<>();
        for (StudentDTOH5 studentDTOH5:queeryAll){
            Map<String,Object> map = new LinkedHashMap<>();
            map.put("姓名",studentDTOH5.getName());
            if (studentDTOH5.isSex()){
                map.put("性别","女");
            }else {
                map.put("性别","男");
            }
            map.put("学号",studentDTOH5.getStudentNum());
            map.put("身份证号码",studentDTOH5.getIdCard());
            map.put("学院",studentDTOH5.getCollege());
            map.put("专业",studentDTOH5.getMajor());
            map.put("入学年份",studentDTOH5.getEnrollmentYear());
            list.add(map);
        }
        FileUtils.downloadExcel(list, response);
    }

    /**
     * 批量导入学生的
     * 空值判断和数据校验
     *
     * @param sheet
     */
    public void judgeNullValue(XSSFSheet sheet) {
        errorNum = 0;
        formatCell(sheet);
        int index = 1;
        String exampleName = "张三";
        //判断示例是否存在
        if (sheet.getRow(index) == null) {
            sheet.createRow(index);
        }
        if (sheet.getRow(index).getCell(3) == null) {
            sheet.getRow(index).createCell(3);
        }
        if (sheet.getRow(index).getCell(3).getStringCellValue().equals(exampleName)) {
            index++;
        }
        if (index > sheet.getLastRowNum()) {
            log.error("该行不能为空");
            String errorString = "该行不能为空！";
            if (sheet.getRow(index) == null) {
                sheet.createRow(index);
                if (sheet.getRow(index).getCell(0) == null) {
                    sheet.getRow(index).createCell(0);
                }
            }
            XSSFCell kongCell = sheet.getRow(index).getCell(0);
            recordErrorCell(kongCell, errorString);
            return;
        }

        for (int i = index; i < sheet.getLastRowNum() + 1; i++) {
//            studentFalse = true;
            Student eStudent = new Student();
            XSSFRow row = sheet.getRow(i);
            //如果该行不为空行
            if (row != null && !isRowEmpty(row)) {
                Cell universityCell = row.getCell(0);
                Cell collegeCell = row.getCell(1);
                Cell majorCell = row.getCell(2);
                Cell nameCell = row.getCell(3);
                Cell sexCell = row.getCell(4);
                Cell studentNumCell = row.getCell(5);
                Cell idCardCell = row.getCell(6);
                Cell admissionNumCell = row.getCell(7);
                Cell dateCell = row.getCell(8);
                //初始化单元格格式为文本类型
                List<Cell> cellList = new ArrayList<>();
                cellList.add(universityCell);
                cellList.add(collegeCell);
                cellList.add(majorCell);
                cellList.add(nameCell);
                cellList.add(sexCell);
                cellList.add(studentNumCell);
                cellList.add(idCardCell);
                cellList.add(admissionNumCell);
                cellList.add(dateCell);
                for (int j = 0; j < cellList.size(); j++) {
                    if (cellList.get(j) == null) {
                        cellList.set(j, row.createCell(j));
                        if (!cellList.get(j).getCellTypeEnum().equals(CellType.STRING)) {
                            cellList.get(j).setCellType(CellType.STRING);
                        }
                    } else {
                        if (!cellList.get(j).getCellTypeEnum().equals(CellType.STRING)) {
                            cellList.get(j).setCellType(CellType.STRING);
                        }
                    }
                }

                String university = cellList.get(0).getStringCellValue();
                String name = cellList.get(3).getStringCellValue();
                String idCard = cellList.get(6).getStringCellValue();
                String college = cellList.get(1).getStringCellValue();
                String sex = cellList.get(4).getStringCellValue();
                String major = cellList.get(2).getStringCellValue();
                String studentNum = cellList.get(5).getStringCellValue();
                String admissionNum = cellList.get(7).getStringCellValue();
                String enrollmentYear = cellList.get(8).getStringCellValue();

                //空值判断
                if (StrUtil.isEmpty(university)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(0), "学校不能为空！");
                    log.error("学校不能为空！");
                } else {
                    //根据学校名称查询学校id
                    Long universityId = studentMapper.selectDeptIdByName(university);
                    if (universityId != null) {
                        eStudent.setUniversityId(universityId);
                    } else {
//                        studentFalse = false;
                        recordErrorCell(cellList.get(0), "该学校在系统中不存在，请先在部门管理中添加学校！");
                        log.error("该学校在系统中不存在，请先在部门管理中添加学校！");
                    }
                }
                if (StrUtil.isEmpty(name)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(3), "姓名不能为空！");
                    log.error("姓名不能为空！");
                } else {
                    eStudent.setName(name);
                }
                if (StrUtil.isEmpty(sex)) {
                    recordErrorCell(cellList.get(4), "性别不能为空！");
                    log.error("性别不能为空！");
                } else {
                    if (sex.equals("男")) {
                        eStudent.setSex(false);
                    } else if (sex.equals("女")) {
                        eStudent.setSex(true);
                    } else {
                        eStudent.setSex(false);
                    }
                }
                if (StrUtil.isEmpty(college)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(1), "学院不能为空！");
                    log.error("学院不能为空！");
                } else {
                    eStudent.setCollege(college);
                }
                if (StrUtil.isEmpty(major)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(2), "专业不能为空！");
                    log.error("专业不能为空！");
                } else {
                    eStudent.setMajor(major);
                }
                if (StrUtil.isEmpty(studentNum)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(5), "学号不能为空！");
                    log.error("学号不能为空！");
                } else {
                    eStudent.setStudentNum(studentNum);
                }
                if (StrUtil.isEmpty(admissionNum)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(7), "准考证号不能为空！");
                    log.error("准考证号不能为空！");
                } else {
                    eStudent.setAdmissionNum(admissionNum);
                }
                if (StrUtil.isEmpty(enrollmentYear)) {
                    recordErrorCell(cellList.get(8), "入学年份不能为空！");
                    log.error("入学年份不能为空！");
                } else {
                    eStudent.setEnrollmentYear(enrollmentYear);
                }
                if (StrUtil.isEmpty(idCard)) {
//                    studentFalse = false;
                    recordErrorCell(cellList.get(6), "身份证号不能为空！");
                    log.error("身份证号不能为空！");
                } else {
                    //验证身份证号是否是18位
                    if (idCard.length() != 18) {
                        recordErrorCell(cellList.get(6), "身份证位数错误，请输入18位有效身份证号！");
                        log.error("身份证位数错误，请输入18位身份证号！");
                    } else {
                        //添加默认密码为123456
                        eStudent.setPassword(passwordEncoder.encode("123456"));
                        eStudent.setIdCard(idCard);
                    }
                }
                Long studentCount = Long.valueOf(studentMapper.selectCount(null));
                //若学生表中有记录
                if (studentCount > 0) {
                    LambdaQueryWrapper<Student> wrapper = new LambdaQueryWrapper<>();
                    wrapper.eq(Student::getStudentNum, studentNum)
                            .eq(Student::getUniversityId, studentMapper.selectDeptIdByName(university)).eq(Student::getDeleted, false);
                    List<Student> students =studentMapper.selectList(wrapper);
                    //若数据库的学生表中同一部门时与excel表中有相同的学号（同一部门，学号不能重复）
                    if (students.size() > 0) {
                        log.error("数据库中该部门内此学号已存在，请勿重复添加！");
//                                studentFalse = false;
                        //给表中的错误信息添加注释
                        String errorString = "该学号已存在数据库中，请勿重复添加！";
                        recordErrorCell(cellList.get(5), errorString);
                    }
                }
                eStudent.setIdentity(1);
                eStudent.setDeleted(false);
            } else {
                //若该行为空行
                log.error("该行不能为空");
                String errorString = "该行不能为空！";
                if (sheet.getRow(i) == null) {
                    sheet.createRow(i);
                }
                if (sheet.getRow(i).getCell(0) == null) {
                    sheet.getRow(i).createCell(0);
                }
                XSSFCell kongCell = sheet.getRow(i).getCell(0);
                recordErrorCell(kongCell, errorString);
                break;
            }
            if (eStudent != null) {
                eStudentList.add(eStudent);
            }
        }
        //为了判断excel中是否有同一学校重复学号的记录
        if (eStudentList.size() > 0) {
            //根据多属性分组
            Map<String, Long> collectMap = eStudentList.stream().filter(o -> o.getUniversityId() != null && o.getStudentNum() != null).collect(Collectors.groupingBy(
                    o -> o.getUniversityId() + "_" + o.getStudentNum(), Collectors.counting()));
            Set<String> keySet = collectMap.keySet();
            for (String key : keySet) {
                Long valueCount = collectMap.get(key);
                //如果excel中有重复学生
                if (valueCount > 1) {
                    if (null != key) {
                        //获取key中的学生的属性，到excel中查找对应的列，加注释
                        String[] properties = key.split("_");
                        //学校id，学号
                        if (!properties[0].equals("")) {
                            Long sameUniversityId = Long.parseLong(properties[0]);
                            String sameStudentNum = properties[1];
                            for (int k = 1; k < sheet.getLastRowNum() + 1; k++) {
                                XSSFRow row1 = sheet.getRow(k);
                                if (isRowEmpty(row1)) {
                                    break;
                                }
                                if (row1 != null && !isRowEmpty(row1)) {
                                    Cell universityCell = row1.getCell(0);
                                    Cell studentNumCell = row1.getCell(5);
                                    if (universityCell != null && studentNumCell != null) {
                                        String universityName = universityCell.getStringCellValue();
                                        if (universityName != null) {
                                            Long deptId =studentMapper.selectDeptIdByName(universityName);
                                            String studentNum = studentNumCell.getStringCellValue();
                                            if (deptId != null && !studentNum.equals("")) {
                                                if (studentNum.equals(sameStudentNum) && deptId.equals(sameUniversityId)) {
                                                    recordErrorCell(studentNumCell, "此excel文件中该学号重复，请仅保留一个！");
                                                    log.error("此excel文件中该学号重复，请仅保留一个！(excel中有重复数据 --> 同一部门学号相同的记录有多条。)");
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    /**
     * 错误信息描述(添加注释、设置单元格背景颜色)
     *
     * @param cell
     * @param error
     */
    public void recordErrorCell(Cell cell, String error) {
        errorNum++;
//        Sheet sheet = writer.getWorkBook().getSheetAt();
        Workbook workbook = cell.getSheet().getWorkbook();
        Sheet sheet = cell.getSheet();
        Drawing draw = sheet.createDrawingPatriarch();
        CreationHelper factory = sheet.getWorkbook().getCreationHelper();
        ClientAnchor anchor = factory.createClientAnchor();
        //前四个参数是坐标点,后四个参数是编辑和显示批注时的大小.
        Comment comment = draw.createCellComment(anchor);
        comment.setString(new XSSFRichTextString(error));  //设置批注内容
        //将批注添加到单元格对象中
        cell.setCellComment(comment);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());  // 背景色
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cell.setCellStyle(cellStyle);
    }

    /**
     * 初始化单元格样式、注释和格式
     *
     * @param sheet
     */
    public void formatCell(XSSFSheet sheet) {
        Workbook workbook = sheet.getWorkbook();
        //遍历列
        if (sheet.getRow(1) != null) {
            for (int a = 0; a < sheet.getRow(1).getPhysicalNumberOfCells(); a++) {
                //遍历列所在行
                for (int i = 0; i < sheet.getLastRowNum() + 1; i++) {
                    //为所有单元格设置为有单元格
                    if (sheet.getRow(i) == null) {
                        sheet.createRow(i);
                    }
                    if (sheet.getRow(i).getCell(a) == null) {
                        sheet.getRow(i).createCell(a);
                    }
                    Cell cell = sheet.getRow(i).getCell(a);
                    //设置默认值和样式
                    if (ObjectUtil.isNotNull(cell)) {
                        CellStyle cellStyle = workbook.createCellStyle();
                        // 背景色
                        cellStyle.setFillPattern(FillPatternType.NO_FILL);
                        cell.setCellStyle(cellStyle);
                        //移除注释
                        cell.removeCellComment();
                    }
                }
            }
        } else {
            Row row1 = sheet.createRow(1);
            Cell cell1 = row1.createCell(0);
            recordErrorCell(cell1, "该行不能为空！");
            log.error("该行不能为空！");
        }
    }
    /**
     * 判断是否为空行
     *
     * @param row
     * @return
     */
    public static boolean isRowEmpty(Row row) {
        for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
            Cell cell = row.getCell(c);
            if (cell != null && !cell.getCellTypeEnum().equals(CellType.BLANK)) {
                return false;
            }
        }
        return true;
    }


}
